﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Reflection;

namespace WorkStation.Frameworks.Core.Util
{
    /// <summary>
    /// * 数据转换
    /// </summary>
    public class DataTransferUtil
    {
        private enum FileType { Csv, Excel };

        private static readonly string CSV_CONN_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Text;FMT=Delimited;HDR=YES;'";
        private static readonly string EXCEL2003_CONN_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES;'";
        private static readonly string EXCEL2007_CONN_STRING = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 12.0 Xml;HDR=YES;'";

        #region * 公共函数

        /// <summary>
        /// * CSV文件转换为DataTable
        /// </summary>
        /// <param name="fileName">文件名称</param>
        /// <returns></returns>
        public static DataTable CsvToDataTable(string fileName)
        {
            return DataTransferUtil.CsvToDataTable(fileName, "Result");
        }

        /// <summary>
        /// * CSV文件转换为DataTable
        /// </summary>
        /// <param name="fileName">文件名称</param>
        /// <param name="tableName">DataTable Name</param>
        /// <returns></returns>
        public static DataTable CsvToDataTable(string fileName, string tableName)
        {
            DataTransferUtil.CheckFile(fileName, FileType.Csv);

            OleDbConnection OleCon = DataTransferUtil.GetConnection(fileName);
            OleDbCommand OleCmd = new OleDbCommand();
            OleDbDataAdapter OleDa = new OleDbDataAdapter();

            string name = fileName.Substring(fileName.LastIndexOf(@"\") + 1);
            string path = fileName.Substring(0, fileName.LastIndexOf(@"\") + 1);

            DataSet CsvData = new DataSet();

            OleCon.Open();
            OleCmd.Connection = OleCon;
            OleCmd.CommandText = "SELECT * FROM " + name;
            OleDa.SelectCommand = OleCmd;

            try
            {
                OleDa.Fill(CsvData, tableName);

                if (CsvData.Tables.Count > 0)
                {
                    foreach (DataColumn column in CsvData.Tables[0].Columns)
                    {
                        column.ColumnName = column.ColumnName.Trim();
                    }

                    return CsvData.Tables[0];
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                throw;
            }

            finally
            {
                OleCon.Close();
                OleCmd.Dispose();
                OleDa.Dispose();
                OleCon.Dispose();
            }
        }

        /// <summary>
        /// * DataTable转换为CSV文件
        /// </summary>
        /// <param name="fileName">输出文件名称</param>
        /// <param name="sTable">数据源</param>
        public static void DataTableToCsv(string fileName, DataTable sTable)
        {
            DataTransferUtil.CheckFile(fileName, FileType.Csv);
            DataTransferUtil.Exists(fileName);

            FileStream stream = File.Create(fileName);
            StreamWriter writer = new StreamWriter(stream, Encoding.GetEncoding("GB2312"));

            try
            {
                DataTransferUtil.WriteCsvHeader(writer, sTable.Columns.Cast<DataColumn>().ToList());
                DataTransferUtil.WriteCsvData(writer, sTable.Rows.Cast<DataRow>().ToList());
            }
            catch (Exception ce)
            {
                throw;
            }
            finally
            {
                writer.Flush();
                stream.Flush();
                writer.Close();
                stream.Close();
            }
        }

        /// <summary>
        /// * DataSet转换为Excel文件
        /// </summary>
        /// <param name="fileName">输出文件名称</param>
        /// <param name="ds">数据源</param>
        public static void DataSetToExcel(string fileName, DataSet ds)
        {
            DataTransferUtil.CheckFile(fileName, FileType.Excel);
            DataTransferUtil.Exists(fileName);

            OleDbConnection OleCon = DataTransferUtil.GetConnection(fileName);

            OleCon.Open();

            try
            {
                foreach (DataTable sTable in ds.Tables)
                {
                    OleDbCommand OleCmd = DataTransferUtil.GetCreateTableCommand(OleCon, sTable);

                    OleCmd.ExecuteNonQuery();

                    OleDbDataAdapter OleDa = new OleDbDataAdapter("SELECT * FROM [" + sTable.TableName + "$]", OleCon);

                    DataTable oTable = new DataTable();

                    OleDa.Fill(oTable);

                    OleDa.InsertCommand = DataTransferUtil.GetInsertCommand(OleCon, sTable);

                    foreach (DataRow dr in sTable.Rows)
                    {
                        DataRow row = oTable.NewRow();

                        foreach (DataColumn dc in sTable.Columns)
                        {
                            row[dc.ColumnName] = dr[dc.ColumnName];
                        }

                        oTable.Rows.Add(row);
                    }

                    OleDa.Update(oTable);
                }
            }
            catch (Exception ce)
            {
                throw ce;
            }
            finally
            {
                if (OleCon != null && OleCon.State == ConnectionState.Open)
                {
                    OleCon.Close();
                }
            }
        }

        /// <summary>
        /// * 实体集合数据转换成DataTable
        /// </summary>
        /// <typeparam name="TEntity">实体类型</typeparam>
        /// <param name="tableName">导出DataTable名称</param>
        /// <param name="entities">实体集合</param>
        /// <param name="columnReference">列映射字典</param>
        /// <returns></returns>
        public static DataTable EntitiesToDataTable<TEntity>(string tableName, List<TEntity> entities, Dictionary<string, string> columnReference)
        {
            DataTable res = new DataTable(tableName);

            Type entityType = typeof(TEntity);

            List<PropertyInfo> outputProperties = new List<PropertyInfo>();

            foreach (KeyValuePair<string, string> pair in columnReference)
            {
                PropertyInfo propertyInfo = entityType.GetProperty(pair.Key);

                if (propertyInfo != null)
                {
                    res.Columns.Add(new DataColumn(pair.Value, typeof(string)));
                    outputProperties.Add(propertyInfo);
                }
            }

            foreach (TEntity entity in entities)
            {
                DataRow newRow = res.NewRow();

                foreach (PropertyInfo propertyInfo in outputProperties)
                {
                    object value = propertyInfo.GetValue(entity, null);
                    newRow[columnReference[propertyInfo.Name]] = value == null ? "" : value.ToString();
                }

                res.Rows.Add(newRow);
            }

            return res;
        }

        /// <summary>
        /// * 实体集合数据转换成DataTable 根据ExcelColumnAttribute标记生成列头 (未测试)
        /// </summary>
        /// <typeparam name="TEntity">实体类型</typeparam>
        /// <param name="tableName">导出DataTable名称</param>
        /// <param name="entities">实体集合</param>
        /// <returns></returns>
        public static DataTable EntitiesToDataTable<TEntity>(string tableName, List<TEntity> entities)
        {
            DataTable res = new DataTable(tableName);

            Type entityType = typeof(TEntity);

            List<PropertyInfo> outputProperties = entityType.GetProperties().Where(v => v.GetCustomAttributes(typeof(ExcelColumnAttribute), true).Count() != 0).ToList();

            foreach (PropertyInfo property in outputProperties)
            {
                string columnName = (property.GetCustomAttributes(typeof(ExcelColumnAttribute), true).ToList()[0] as ExcelColumnAttribute).ColumnName;
                res.Columns.Add(new DataColumn(columnName, typeof(string)));
            }

            foreach (TEntity entity in entities)
            {
                DataRow newRow = res.NewRow();

                foreach (PropertyInfo propertyInfo in outputProperties)
                {
                    object value = propertyInfo.GetValue(entity, null);
                    string columnName = (propertyInfo.GetCustomAttributes(typeof(ExcelColumnAttribute), true).ToList()[0] as ExcelColumnAttribute).ColumnName;
                    newRow[columnName] = value == null ? "" : value.ToString();
                }

                res.Rows.Add(newRow);
            }

            return res;
        }

        /// <summary>
        /// * DataTable转换为Excel文件
        /// </summary>
        /// <param name="fileName">输出文件名称</param>
        /// <param name="sTable">数据源</param>
        public static void DataTableToExcel(string fileName, DataTable sTable)
        {
            DataTransferUtil.CheckFile(fileName, FileType.Excel);
            DataTransferUtil.Exists(fileName);

            OleDbConnection OleCon = DataTransferUtil.GetConnection(fileName);

            OleCon.Open();

            try
            {
                OleDbCommand OleCmd = DataTransferUtil.GetCreateTableCommand(OleCon, sTable);

                OleCmd.ExecuteNonQuery();

                OleDbDataAdapter OleDa = new OleDbDataAdapter("SELECT * FROM [" + sTable.TableName + "$]", OleCon);

                DataTable oTable = new DataTable();

                OleDa.Fill(oTable);

                OleDa.InsertCommand = DataTransferUtil.GetInsertCommand(OleCon, sTable);

                foreach (DataRow dr in sTable.Rows)
                {
                    DataRow row = oTable.NewRow();

                    foreach (DataColumn dc in sTable.Columns)
                    {
                        row[dc.ColumnName] = dr[dc.ColumnName];
                    }

                    oTable.Rows.Add(row);
                }

                OleDa.Update(oTable);
            }
            catch (Exception ce)
            {
                throw ce;
            }
            finally
            {
                if (OleCon != null && OleCon.State == ConnectionState.Open)
                {
                    OleCon.Close();
                }
            }
        }

        /// <summary>
        /// * DataTable转换为Excel
        /// </summary>
        /// <param name="fileName">文件名称</param>
        /// <param name="sTable">数据源</param>
        /// <param name="columnReference">列映射字典</param>
        public static void DataTableToExcel(string fileName, DataTable sTable, Dictionary<string, string> columnReference)
        {
            DataTransferUtil.CheckFile(fileName, FileType.Excel);
            DataTransferUtil.Exists(fileName);

            OleDbConnection OleCon = DataTransferUtil.GetConnection(fileName);

            OleCon.Open();

            try
            {
                OleDbCommand OleCmd = DataTransferUtil.GetCreateTableCommand(OleCon, sTable, columnReference);

                OleCmd.ExecuteNonQuery();

                OleDbDataAdapter OleDa = new OleDbDataAdapter("SELECT * FROM [" + sTable.TableName + "$]", OleCon);

                DataTable oTable = new DataTable();

                OleDa.Fill(oTable);

                OleDa.InsertCommand = DataTransferUtil.GetInsertCommand(OleCon, sTable, columnReference);

                foreach (DataRow dr in sTable.Rows)
                {
                    DataRow row = oTable.NewRow();

                    foreach (DataColumn dc in sTable.Columns)
                    {
                        if (columnReference.ContainsKey(dc.ColumnName))
                        {
                            row[columnReference[dc.ColumnName]] = dr[dc.ColumnName];
                        }
                    }

                    oTable.Rows.Add(row);
                }

                OleDa.Update(oTable);
            }
            catch (Exception ce)
            {
                throw ce;
            }
            finally
            {
                if (OleCon != null && OleCon.State == ConnectionState.Open)
                {
                    OleCon.Close();
                }
            }
        }

        /// <summary>
        /// * Excel文件转换为DataSet
        /// </summary>
        /// <param name="fileName">文件名称</param>
        /// <returns></returns>
        public static DataSet ExcelToDataSet(string fileName)
        {
            DataTransferUtil.CheckFile(fileName, FileType.Excel);

            OleDbConnection OleCon = DataTransferUtil.GetConnection(fileName);

            OleCon.Open();

            DataTable schemaDt = OleCon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            OleDbDataAdapter OleDa = new OleDbDataAdapter();

            DataSet res = new DataSet("Result");

            try
            {
                foreach (DataRow row in schemaDt.Rows)
                {
                    string sheetName = row["TABLE_NAME"].ToString();

                    OleDbCommand OleCmd = OleCon.CreateCommand();

                    OleCmd.CommandText = "SELECT * FROM [" + sheetName + "]";
                    OleCmd.Connection = OleCon;

                    OleDa.SelectCommand = OleCmd;

                    OleDa.Fill(res, sheetName.Replace("$", ""));
                }
            }
            catch (Exception ce)
            {
                throw;
            }
            finally
            {
                if (OleCon != null && OleCon.State == ConnectionState.Open)
                {
                    OleCon.Close();
                }
            }

            return res;
        }

        #endregion

        #region * 私有函数

        /// <summary>
        /// * 写CSV文件内容数据
        /// </summary>
        /// <param name="writer">StreamWriter</param>
        /// <param name="rows">数据行集合</param>
        private static void WriteCsvData(StreamWriter writer, List<DataRow> rows)
        {
            StringBuilder buffer = new StringBuilder(string.Empty);

            foreach (DataRow row in rows)
            {
                foreach (DataColumn column in row.Table.Columns)
                {
                    buffer.Append(string.Format("\"{0}\",", row[column].ToString()));
                }

                buffer = buffer.Remove(buffer.Length - 1, 1).Append("\r\n");

                writer.Write(buffer.ToString());

                buffer = buffer.Remove(0, buffer.Length);
            }
        }

        /// <summary>
        /// * 写CSV文件列头数据
        /// </summary>
        /// <param name="writer">StreamWriter</param>
        /// <param name="columns">数据列集合</param>
        private static void WriteCsvHeader(StreamWriter writer, List<DataColumn> columns)
        {
            StringBuilder buffer = new StringBuilder(string.Empty);

            foreach (DataColumn column in columns)
            {
                buffer.Append(string.Format("\"{0}\",", column.ColumnName));
            }

            buffer = buffer.Remove(buffer.Length - 1, 1).Append("\r\n");

            writer.Write(buffer.ToString());
        }

        /// <summary>
        /// * 根据文件类型获取连接
        /// </summary>
        /// <param name="fileName">文件名称</param>
        /// <returns></returns>
        private static OleDbConnection GetConnection(string fileName)
        {
            OleDbConnection res = new OleDbConnection();

            if (fileName.ToUpper().EndsWith("CSV"))
            {
                res.ConnectionString = string.Format(DataTransferUtil.CSV_CONN_STRING, fileName.Substring(0, fileName.LastIndexOf(@"\") + 1));
            }

            if (fileName.ToUpper().EndsWith("XLSX"))
            {
                res.ConnectionString = string.Format(DataTransferUtil.EXCEL2007_CONN_STRING, fileName);
            }

            if (fileName.ToUpper().EndsWith("XLS"))
            {
                res.ConnectionString = string.Format(DataTransferUtil.EXCEL2003_CONN_STRING, fileName);
            }

            return res;
        }

        /// <summary>
        /// * 获得插入命令
        /// </summary>
        /// <param name="OleCon">连接</param>
        /// <param name="dt">数据源</param>
        /// <returns></returns>
        private static OleDbCommand GetInsertCommand(OleDbConnection OleCon, DataTable dt)
        {
            OleDbCommand res = OleCon.CreateCommand();

            res.CommandText = "INSERT INTO [" + dt.TableName + "$] (";

            foreach (DataColumn dc in dt.Columns)
            {
                res.CommandText += "[" + dc.ColumnName + "] ";
                res.CommandText += ",";
            }

            res.CommandText = res.CommandText.Substring(0, res.CommandText.Length - 1) + ") VALUES (";

            foreach (DataColumn dc in dt.Columns)
            {
                res.CommandText += "?,";
            }

            res.CommandText = res.CommandText.Substring(0, res.CommandText.Length - 1) + ")";

            foreach (DataColumn dc in dt.Columns)
            {
                res.Parameters.Add("@" + dc.ColumnName, DataTransferUtil.GetDataType(dc.DataType), 0, dc.ColumnName);
            }

            return res;
        }

        private static OleDbCommand GetInsertCommand(OleDbConnection OleCon, DataTable dt, Dictionary<string, string> columnReference)
        {
            OleDbCommand res = OleCon.CreateCommand();

            res.CommandText = "INSERT INTO [" + dt.TableName + "$] (";

            foreach (DataColumn dc in dt.Columns)
            {
                if (columnReference.ContainsKey(dc.ColumnName))
                {
                    res.CommandText += "[" + columnReference[dc.ColumnName] + "] ";
                    res.CommandText += ",";
                }
            }

            res.CommandText = res.CommandText.Substring(0, res.CommandText.Length - 1) + ") VALUES (";

            foreach (DataColumn dc in dt.Columns)
            {
                if (columnReference.ContainsKey(dc.ColumnName))
                {
                    res.CommandText += "?,";
                }
            }

            res.CommandText = res.CommandText.Substring(0, res.CommandText.Length - 1) + ")";

            foreach (DataColumn dc in dt.Columns)
            {
                if (columnReference.ContainsKey(dc.ColumnName))
                {
                    res.Parameters.Add("@" + dc.ColumnName, DataTransferUtil.GetDataType(dc.DataType), 0, columnReference[dc.ColumnName]);
                }
            }

            return res;
        }

        /// <summary>
        /// * 获得建立Sheet命令
        /// </summary>
        /// <param name="OleCon"></param>
        /// <param name="dt"></param>
        /// <returns></returns>
        private static OleDbCommand GetCreateTableCommand(OleDbConnection OleCon, DataTable dt)
        {
            OleDbCommand res = OleCon.CreateCommand();

            res.Connection = OleCon;
            res.CommandText = "CREATE TABLE " + dt.TableName + " (";

            foreach (DataColumn dc in dt.Columns)
            {
                res.CommandText += "[" + dc.ColumnName + "] " + DataTransferUtil.GetDataType(dc.DataType).ToString() + " ,";
            }

            res.CommandText = res.CommandText.Substring(0, res.CommandText.Length - 1) + ")";

            return res;
        }

        private static OleDbCommand GetCreateTableCommand(OleDbConnection OleCon, DataTable dt, Dictionary<string, string> columnReference)
        {
            OleDbCommand res = OleCon.CreateCommand();

            res.Connection = OleCon;
            res.CommandText = "CREATE TABLE " + dt.TableName + " (";

            foreach (DataColumn dc in dt.Columns)
            {
                if (columnReference.ContainsKey(dc.ColumnName))
                {
                    res.CommandText += "[" + columnReference[dc.ColumnName] + "] " + DataTransferUtil.GetDataType(dc.DataType).ToString() + " ,";
                }
            }

            res.CommandText = res.CommandText.Substring(0, res.CommandText.Length - 1) + ")";

            return res;
        }

        /// <summary>
        /// * 根据Type获取OleDbType
        /// </summary>
        /// <param name="type"></param>
        /// <returns></returns>
        private static OleDbType GetDataType(Type type)
        {
            OleDbType res = OleDbType.Char;

            switch (type.Name)
            {
                case "Int32":
                    res = OleDbType.Double;
                    break;

                case "Double":
                    res = OleDbType.Double;
                    break;

                case "Decimal":
                    res = OleDbType.Double;
                    break;
            }

            return res;
        }

        /// <summary>
        /// * 检查文件格式
        /// </summary>
        /// <param name="fileName">文件名称</param>
        /// <param name="fileType">文件类型</param>
        private static void CheckFile(string fileName, FileType fileType)
        {
            switch (fileType)
            {
                case FileType.Csv:

                    if (!fileName.ToUpper().EndsWith("CSV"))
                    {
                        throw new Exception("该文件不是CSV文件格式。");
                    }

                    break;

                case FileType.Excel:

                    if (!fileName.ToUpper().EndsWith("XLSX") && !fileName.ToUpper().EndsWith("XLS"))
                    {
                        throw new Exception("该文件不是Excel格式文件。");
                    }

                    break;
            }
        }

        /// <summary>
        /// * 检查已经存在的文件
        /// </summary>
        /// <param name="fileName">文件名称</param>
        private static void Exists(string fileName)
        {
            if (File.Exists(fileName))
            {
                File.Delete(fileName);
            }
        }

        #endregion
    }
}
